{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Neeps - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "**co.CHt is to be given all the teaching that co.ACg currently does. Identify those events which will clash.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>cht</th>\n",
       "        <th>acg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co12005.T03</td>\n",
       "        <td>co12005.T01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co12005.T03</td>\n",
       "        <td>co72013.L02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co12005.T03</td>\n",
       "        <td>co72013.T03</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('co12005.T03', 'co12005.T01'),\n",
       " ('co12005.T03', 'co72013.L02'),\n",
       " ('co12005.T03', 'co72013.T03')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (-- list all the events\n",
    "  SELECT ut_event.id, ut_occurs.week, ut_event.dow,\n",
    "    ut_teaches.staff, CAST(SPLIT(ut_event.tod, ':')[0] AS INT) AS begin, \n",
    "    CAST(SPLIT(ut_event.tod, ':')[0] AS INT) + ut_event.duration AS due\n",
    "      FROM ut_event JOIN ut_teaches ON (ut_event.id=ut_teaches.event) JOIN\n",
    "        ut_occurs ON (ut_event.id=ut_occurs.event)\n",
    ")\n",
    "SELECT DISTINCT a.id `co.CHt`, b.id `co.ACg`\n",
    "  FROM (SELECT * FROM t WHERE staff='co.CHt') a JOIN\n",
    "    (SELECT * FROM t WHERE staff='co.ACg') b ON (\n",
    "        a.week=b.week AND a.dow=b.dow)\n",
    "    WHERE\n",
    "        (a.begin>=b.begin AND a.begin<b.due OR\n",
    "         b.begin>=a.begin AND b.begin<a.due)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "**Produce a table showing the utilisation rate and the occupancy level for all rooms with a capacity more than 60.**\n",
    "\n",
    "> I don't know how 'utilisation rate' and 'occupancy level' are defined."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>room</th>\n",
       "        <th>util</th>\n",
       "        <th>occup_level</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>cr.SMH</td>\n",
       "        <td>2.4</td>\n",
       "        <td>0.27</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('cr.SMH', 2.4, 0.27)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH hrs AS (\n",
    "    -- theoretical full utilisation hours: 15 weeks * 5 days * 12 hours\n",
    "    SELECT MAX(CAST(SPLIT(tod, ':')[0] AS INT) + duration) - \n",
    "             MIN(CAST(SPLIT(tod, ':')[0] AS INT)) hrs\n",
    "        FROM ut_event\n",
    "), t AS (\n",
    "  SELECT SUM(hrs) tot_hrs FROM\n",
    "    (SELECT ut_week.id, hrs.hrs\n",
    "      FROM ut_week JOIN hrs) a\n",
    "), s AS ( -- attended students per event\n",
    "  SELECT room, SUM(sze) size, SUM(capacity) capacity FROM\n",
    "    (SELECT ut_event.id evt, ut_occurs.week, ut_event.room, \n",
    "        ut_student.sze, ut_room.capacity FROM \n",
    "        ut_event JOIN ut_occurs ON (ut_event.id=ut_occurs.event) JOIN\n",
    "        ut_attends ON (ut_event.id=ut_attends.event) JOIN\n",
    "        ut_student ON (ut_student.id=ut_attends.student) JOIN\n",
    "        ut_room ON (ut_event.room=ut_room.id)\n",
    "     ) a\n",
    "     GROUP BY room\n",
    ")\n",
    "SELECT ut_event.room, ROUND(SUM(duration)/t.tot_hrs, 2) util,\n",
    "    ROUND(SUM(s.size)/SUM(s.capacity), 2) occup_level\n",
    "    FROM ut_event JOIN ut_occurs ON (\n",
    "        ut_event.id=ut_occurs.event) JOIN \n",
    "    ut_room ON (\n",
    "        ut_event.room=ut_room.id) JOIN\n",
    "    s ON (ut_event.room=s.room) JOIN\n",
    "    t\n",
    "        WHERE ut_room.capacity>60\n",
    "        GROUP BY ut_event.room, t.tot_hrs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "**A one hour staff meeting is to be held between 09:00 and 17:00. Events which clash are to be cancelled. Identify the hour which will result in the least disruption.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>week</th>\n",
       "        <th>m09</th>\n",
       "        <th>m10</th>\n",
       "        <th>m11</th>\n",
       "        <th>m12</th>\n",
       "        <th>m13</th>\n",
       "        <th>m14</th>\n",
       "        <th>m15</th>\n",
       "        <th>m16</th>\n",
       "        <th>m17</th>\n",
       "        <th>t09</th>\n",
       "        <th>t10</th>\n",
       "        <th>t11</th>\n",
       "        <th>t12</th>\n",
       "        <th>t13</th>\n",
       "        <th>t14</th>\n",
       "        <th>t15</th>\n",
       "        <th>t16</th>\n",
       "        <th>t17</th>\n",
       "        <th>w09</th>\n",
       "        <th>w10</th>\n",
       "        <th>w11</th>\n",
       "        <th>w12</th>\n",
       "        <th>w13</th>\n",
       "        <th>w14</th>\n",
       "        <th>w15</th>\n",
       "        <th>w16</th>\n",
       "        <th>w17</th>\n",
       "        <th>th09</th>\n",
       "        <th>th10</th>\n",
       "        <th>th11</th>\n",
       "        <th>th12</th>\n",
       "        <th>th13</th>\n",
       "        <th>th14</th>\n",
       "        <th>th15</th>\n",
       "        <th>th16</th>\n",
       "        <th>th17</th>\n",
       "        <th>f09</th>\n",
       "        <th>f10</th>\n",
       "        <th>f11</th>\n",
       "        <th>f12</th>\n",
       "        <th>f13</th>\n",
       "        <th>f14</th>\n",
       "        <th>f15</th>\n",
       "        <th>f16</th>\n",
       "        <th>f17</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>01</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>14</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>02</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>14</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>03</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>14</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>04</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>14</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>05</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>06</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>07</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>08</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>9</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>09</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "        <td>6</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "        <td>6</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "        <td>6</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>6</td>\n",
       "        <td>16</td>\n",
       "        <td>4</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "        <td>18</td>\n",
       "        <td>14</td>\n",
       "        <td>4</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>8</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>8</td>\n",
       "        <td>14</td>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>6</td>\n",
       "        <td>10</td>\n",
       "        <td>8</td>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "        <td>6</td>\n",
       "        <td>12</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('01', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 10, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 14, 10, 8, 6, 10, 8, 6, 6, 4, 14, 2),\n",
       " ('02', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 10, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 14, 10, 8, 6, 10, 8, 6, 6, 4, 14, 2),\n",
       " ('03', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 10, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 14, 10, 8, 6, 10, 8, 6, 6, 4, 14, 2),\n",
       " ('04', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 10, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 14, 10, 8, 6, 10, 8, 4, 6, 4, 14, 2),\n",
       " ('05', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 10, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 14, 10, 8, 6, 10, 8, 4, 6, 4, 12, 2),\n",
       " ('06', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 10, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 14, 10, 8, 6, 10, 8, 4, 6, 4, 12, 2),\n",
       " ('07', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 10, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 14, 10, 8, 6, 10, 8, 4, 6, 4, 12, 2),\n",
       " ('08', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 9, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 14, 10, 8, 6, 10, 8, 4, 6, 4, 12, 2),\n",
       " ('09', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
       " ('10', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 10, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 12, 10, 8, 6, 10, 8, 4, 6, 6, 12, 2),\n",
       " ('11', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 10, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 12, 10, 8, 6, 10, 8, 4, 6, 6, 12, 2),\n",
       " ('12', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 10, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 12, 10, 8, 6, 10, 8, 4, 6, 6, 12, 2),\n",
       " ('13', 2, 6, 2, 14, 4, 10, 6, 16, 4, 4, 10, 18, 14, 4, 12, 10, 12, 2, 10, 10, 10, 12, 8, 6, 8, 8, 4, 2, 14, 10, 12, 8, 14, 10, 12, 10, 8, 6, 10, 8, 4, 6, 6, 12, 2),\n",
       " ('14', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),\n",
       " ('15', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS ( -- all the events per hour\n",
    "  SELECT * FROM\n",
    "    (SELECT ut_occurs.event, ut_occurs.week, ut_event.dow, \n",
    "       CAST(SPLIT(ut_event.tod, ':')[0] AS INT) tod\n",
    "       FROM ut_event JOIN ut_occurs ON (\n",
    "           ut_event.id=ut_occurs.event)\n",
    "       WHERE ut_event.duration=1\n",
    "    UNION ALL\n",
    "     SELECT ut_occurs.event, ut_occurs.week, ut_event.dow, \n",
    "       CAST(SPLIT(ut_event.tod, ':')[0] AS INT) + 1 AS tod\n",
    "       FROM ut_event JOIN ut_occurs ON (\n",
    "           ut_event.id=ut_occurs.event)\n",
    "       WHERE ut_event.duration=2) AS a\n",
    ")\n",
    "SELECT ut_week.id week, \n",
    "  SUM(CASE WHEN dow='Monday' AND tod=9 THEN 1 ELSE 0 END) m09,\n",
    "    SUM(CASE WHEN dow='Monday' AND tod=10 THEN 1 ELSE 0 END) m10,\n",
    "    SUM(CASE WHEN dow='Monday' AND tod=11 THEN 1 ELSE 0 END) m11,\n",
    "    SUM(CASE WHEN dow='Monday' AND tod=12 THEN 1 ELSE 0 END) m12,\n",
    "    SUM(CASE WHEN dow='Monday' AND tod=13 THEN 1 ELSE 0 END) m13,\n",
    "    SUM(CASE WHEN dow='Monday' AND tod=14 THEN 1 ELSE 0 END) m14,\n",
    "    SUM(CASE WHEN dow='Monday' AND tod=15 THEN 1 ELSE 0 END) m15,\n",
    "    SUM(CASE WHEN dow='Monday' AND tod=16 THEN 1 ELSE 0 END) m16,\n",
    "    SUM(CASE WHEN dow='Monday' AND tod=17 THEN 1 ELSE 0 END) m17,    \n",
    "    SUM(CASE WHEN dow='Tuesday' AND tod=9 THEN 1 ELSE 0 END) t09,\n",
    "    SUM(CASE WHEN dow='Tuesday' AND tod=10 THEN 1 ELSE 0 END) t10,\n",
    "    SUM(CASE WHEN dow='Tuesday' AND tod=11 THEN 1 ELSE 0 END) t11,\n",
    "    SUM(CASE WHEN dow='Tuesday' AND tod=12 THEN 1 ELSE 0 END) t12,\n",
    "    SUM(CASE WHEN dow='Tuesday' AND tod=13 THEN 1 ELSE 0 END) t13,\n",
    "    SUM(CASE WHEN dow='Tuesday' AND tod=14 THEN 1 ELSE 0 END) t14,\n",
    "    SUM(CASE WHEN dow='Tuesday' AND tod=15 THEN 1 ELSE 0 END) t15,\n",
    "    SUM(CASE WHEN dow='Tuesday' AND tod=16 THEN 1 ELSE 0 END) t16,\n",
    "    SUM(CASE WHEN dow='Tuesday' AND tod=17 THEN 1 ELSE 0 END) t17,    \n",
    "    SUM(CASE WHEN dow='Wednesday' AND tod=9 THEN 1 ELSE 0 END) w09,\n",
    "    SUM(CASE WHEN dow='Wednesday' AND tod=10 THEN 1 ELSE 0 END) w10,\n",
    "    SUM(CASE WHEN dow='Wednesday' AND tod=11 THEN 1 ELSE 0 END) w11,\n",
    "    SUM(CASE WHEN dow='Wednesday' AND tod=12 THEN 1 ELSE 0 END) w12,\n",
    "    SUM(CASE WHEN dow='Wednesday' AND tod=13 THEN 1 ELSE 0 END) w13,\n",
    "    SUM(CASE WHEN dow='Wednesday' AND tod=14 THEN 1 ELSE 0 END) w14,\n",
    "    SUM(CASE WHEN dow='Wednesday' AND tod=15 THEN 1 ELSE 0 END) w15,\n",
    "    SUM(CASE WHEN dow='Wednesday' AND tod=16 THEN 1 ELSE 0 END) w16,\n",
    "    SUM(CASE WHEN dow='Wednesday' AND tod=17 THEN 1 ELSE 0 END) w17,    \n",
    "    SUM(CASE WHEN dow='Thursday' AND tod=9 THEN 1 ELSE 0 END) th09,\n",
    "    SUM(CASE WHEN dow='Thursday' AND tod=10 THEN 1 ELSE 0 END) th10,\n",
    "    SUM(CASE WHEN dow='Thursday' AND tod=11 THEN 1 ELSE 0 END) th11,\n",
    "    SUM(CASE WHEN dow='Thursday' AND tod=12 THEN 1 ELSE 0 END) th12,\n",
    "    SUM(CASE WHEN dow='Thursday' AND tod=13 THEN 1 ELSE 0 END) th13,\n",
    "    SUM(CASE WHEN dow='Thursday' AND tod=14 THEN 1 ELSE 0 END) th14,\n",
    "    SUM(CASE WHEN dow='Thursday' AND tod=15 THEN 1 ELSE 0 END) th15,\n",
    "    SUM(CASE WHEN dow='Thursday' AND tod=16 THEN 1 ELSE 0 END) th16,\n",
    "    SUM(CASE WHEN dow='Thursday' AND tod=17 THEN 1 ELSE 0 END) th17,    \n",
    "    SUM(CASE WHEN dow='Friday' AND tod=9 THEN 1 ELSE 0 END) f09,\n",
    "    SUM(CASE WHEN dow='Friday' AND tod=10 THEN 1 ELSE 0 END) f10,\n",
    "    SUM(CASE WHEN dow='Friday' AND tod=11 THEN 1 ELSE 0 END) f11,\n",
    "    SUM(CASE WHEN dow='Friday' AND tod=12 THEN 1 ELSE 0 END) f12,\n",
    "    SUM(CASE WHEN dow='Friday' AND tod=13 THEN 1 ELSE 0 END) f13,\n",
    "    SUM(CASE WHEN dow='Friday' AND tod=14 THEN 1 ELSE 0 END) f14,\n",
    "    SUM(CASE WHEN dow='Friday' AND tod=15 THEN 1 ELSE 0 END) f15,\n",
    "    SUM(CASE WHEN dow='Friday' AND tod=16 THEN 1 ELSE 0 END) f16,\n",
    "    SUM(CASE WHEN dow='Friday' AND tod=17 THEN 1 ELSE 0 END) f17\n",
    "    FROM t RIGHT JOIN ut_week ON (ut_week.id=t.week)\n",
    "    GROUP BY ut_week.id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>tod</th>\n",
       "        <th>dow</th>\n",
       "        <th>n</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Monday</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Thursday</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>Monday</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>Friday</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>Tuesday</td>\n",
       "        <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Tuesday</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Monday</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Tuesday</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>Monday</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>Wednesday</td>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Friday</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>Friday</td>\n",
       "        <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Friday</td>\n",
       "        <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Monday</td>\n",
       "        <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>Friday</td>\n",
       "        <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>Wednesday</td>\n",
       "        <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>Monday</td>\n",
       "        <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>Friday</td>\n",
       "        <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>Friday</td>\n",
       "        <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>Thursday</td>\n",
       "        <td>96</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">45 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[(9, 'Monday', 24),\n",
       " (9, 'Thursday', 24),\n",
       " (11, 'Monday', 24),\n",
       " (17, 'Friday', 24),\n",
       " (17, 'Tuesday', 24),\n",
       " (9, 'Tuesday', 48),\n",
       " (13, 'Monday', 48),\n",
       " (13, 'Tuesday', 48),\n",
       " (17, 'Monday', 48),\n",
       " (17, 'Wednesday', 48),\n",
       " (13, 'Friday', 54),\n",
       " (15, 'Friday', 56),\n",
       " (10, 'Friday', 72),\n",
       " (10, 'Monday', 72),\n",
       " (14, 'Friday', 72),\n",
       " (14, 'Wednesday', 72),\n",
       " (15, 'Monday', 72),\n",
       " (9, 'Friday', 96),\n",
       " (12, 'Friday', 96),\n",
       " (13, 'Thursday', 96),\n",
       " (13, 'Wednesday', 96),\n",
       " (15, 'Wednesday', 96),\n",
       " (16, 'Wednesday', 96),\n",
       " (11, 'Wednesday', 119),\n",
       " (9, 'Wednesday', 120),\n",
       " (10, 'Tuesday', 120),\n",
       " (10, 'Wednesday', 120),\n",
       " (11, 'Friday', 120),\n",
       " (11, 'Thursday', 120),\n",
       " (14, 'Monday', 120),\n",
       " (15, 'Thursday', 120),\n",
       " (15, 'Tuesday', 120),\n",
       " (17, 'Thursday', 120),\n",
       " (12, 'Thursday', 144),\n",
       " (12, 'Wednesday', 144),\n",
       " (14, 'Tuesday', 144),\n",
       " (16, 'Tuesday', 144),\n",
       " (16, 'Friday', 152),\n",
       " (16, 'Thursday', 160),\n",
       " (10, 'Thursday', 168),\n",
       " (12, 'Monday', 168),\n",
       " (12, 'Tuesday', 168),\n",
       " (14, 'Thursday', 168),\n",
       " (16, 'Monday', 192),\n",
       " (11, 'Tuesday', 216)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "-- You may find that week '09', '14', '15' are totally vacant\n",
    "-- or you can choose Mon 9am/12pm, Tue 5pm, Thu 9am, Fri 5pm \n",
    "    -- all the events per hour\n",
    "  SELECT * FROM\n",
    "    (SELECT ut_occurs.event, ut_occurs.week, ut_event.dow, \n",
    "       CAST(SPLIT(ut_event.tod, ':')[0] AS INT) tod\n",
    "       FROM ut_event JOIN ut_occurs ON (\n",
    "           ut_event.id=ut_occurs.event)\n",
    "       WHERE ut_event.duration=1\n",
    "    UNION ALL\n",
    "     SELECT ut_occurs.event, ut_occurs.week, ut_event.dow, \n",
    "       CAST(SPLIT(ut_event.tod, ':')[0] AS INT) + 1 AS tod\n",
    "       FROM ut_event JOIN ut_occurs ON (\n",
    "           ut_event.id=ut_occurs.event) \n",
    "       WHERE ut_event.duration=2) AS a\n",
    ")\n",
    "SELECT tod, dow, COUNT(*) n\n",
    "    FROM t\n",
    "    WHERE tod BETWEEN 9 AND 17\n",
    "    GROUP BY tod, dow\n",
    "    ORDER BY n, tod, dow"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "**Find all clashes - include the events which clash and the staff, student or rooms that they have in common.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>huh</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>don&#x27;t know how &#x27;clash&#x27; is defined</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(\"don't know how 'clash' is defined\",)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT 'don\\'t know how \\'clash\\' is defined' AS huh FROM ut_event LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "**Produce a timetable for a group of full time students for week 1**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>student</th>\n",
       "        <th>tod</th>\n",
       "        <th>mon</th>\n",
       "        <th>tue</th>\n",
       "        <th>wed</th>\n",
       "        <th>thu</th>\n",
       "        <th>fri</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co.12008.Ea</td>\n",
       "        <td>12</td>\n",
       "        <td>None</td>\n",
       "        <td>co12008.L01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co.12008.Ea</td>\n",
       "        <td>14</td>\n",
       "        <td>co12008.T01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co.12008.Eb</td>\n",
       "        <td>12</td>\n",
       "        <td>None</td>\n",
       "        <td>co12008.L01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co.12008.Eb</td>\n",
       "        <td>15</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>co12008.T02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co.12012.E</td>\n",
       "        <td>12</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>co12012.L01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co.12012.E</td>\n",
       "        <td>14</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>co12012.T01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co.22022.E</td>\n",
       "        <td>9</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>co22022.T01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co.22022.E</td>\n",
       "        <td>11</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>co22022.L01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.BAe</td>\n",
       "        <td>10</td>\n",
       "        <td>co12011.L01</td>\n",
       "        <td>None</td>\n",
       "        <td>co12004.T05</td>\n",
       "        <td>co12006.L02</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.BAe</td>\n",
       "        <td>11</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>co12004.L01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.BAe</td>\n",
       "        <td>12</td>\n",
       "        <td>co12006.T04</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.BAe</td>\n",
       "        <td>14</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>co12011.T01</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.CO</td>\n",
       "        <td>10</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>co12006.L01</td>\n",
       "        <td>None</td>\n",
       "        <td>co12005.L02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.CO</td>\n",
       "        <td>11</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>co12004.L01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.CO</td>\n",
       "        <td>13</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>co12006.L03</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.CO</td>\n",
       "        <td>14</td>\n",
       "        <td>co12005.L01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.CO.a</td>\n",
       "        <td>12</td>\n",
       "        <td>co12004.T01</td>\n",
       "        <td>co12005.T01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.CO.a</td>\n",
       "        <td>15</td>\n",
       "        <td>None</td>\n",
       "        <td>co12006.T01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.CO.c</td>\n",
       "        <td>12</td>\n",
       "        <td>co12004.T01</td>\n",
       "        <td>co12005.T01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>co1.CO.c</td>\n",
       "        <td>15</td>\n",
       "        <td>None</td>\n",
       "        <td>co12006.T01</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">276 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('co.12008.Ea', 12, None, 'co12008.L01', None, None, None),\n",
       " ('co.12008.Ea', 14, 'co12008.T01', None, None, None, None),\n",
       " ('co.12008.Eb', 12, None, 'co12008.L01', None, None, None),\n",
       " ('co.12008.Eb', 15, None, None, None, None, 'co12008.T02'),\n",
       " ('co.12012.E', 12, None, None, 'co12012.L01', None, None),\n",
       " ('co.12012.E', 14, None, None, 'co12012.T01', None, None),\n",
       " ('co.22022.E', 9, None, None, None, None, 'co22022.T01'),\n",
       " ('co.22022.E', 11, None, None, None, None, 'co22022.L01'),\n",
       " ('co1.BAe', 10, 'co12011.L01', None, 'co12004.T05', 'co12006.L02', None),\n",
       " ('co1.BAe', 11, None, None, 'co12004.L01', None, None),\n",
       " ('co1.BAe', 12, 'co12006.T04', None, None, None, None),\n",
       " ('co1.BAe', 14, None, None, None, 'co12011.T01', None),\n",
       " ('co1.CO', 10, None, None, 'co12006.L01', None, 'co12005.L02'),\n",
       " ('co1.CO', 11, None, None, 'co12004.L01', None, None),\n",
       " ('co1.CO', 13, None, None, 'co12006.L03', None, None),\n",
       " ('co1.CO', 14, 'co12005.L01', None, None, None, None),\n",
       " ('co1.CO.a', 12, 'co12004.T01', 'co12005.T01', None, None, None),\n",
       " ('co1.CO.a', 15, None, 'co12006.T01', None, None, None),\n",
       " ('co1.CO.c', 12, 'co12004.T01', 'co12005.T01', None, None, None),\n",
       " ('co1.CO.c', 15, None, 'co12006.T01', None, None, None),\n",
       " ('co1.CO.d', 12, 'co12005.T02', None, None, None, None),\n",
       " ('co1.CO.d', 14, None, None, None, 'co12006.T02', None),\n",
       " ('co1.CO.d', 16, None, 'co12004.T02', None, None, None),\n",
       " ('co1.CO.e', 12, 'co12005.T02', None, None, None, None),\n",
       " ('co1.CO.e', 14, None, None, None, 'co12006.T02', None),\n",
       " ('co1.CO.e', 16, None, 'co12004.T02', None, None, None),\n",
       " ('co1.CO.f', 11, None, 'co12005.T03', None, None, None),\n",
       " ('co1.CO.f', 14, None, 'co12004.T06', None, None, None),\n",
       " ('co1.CO.f', 16, 'co12006.T03', None, None, None, None),\n",
       " ('co1.CO.h', 11, None, 'co12005.T03', None, None, None),\n",
       " ('co1.CO.h', 14, None, 'co12004.T06', None, None, None),\n",
       " ('co1.CO.h', 16, 'co12006.T03', None, None, None, None),\n",
       " ('co1.CO.i', 12, 'co12006.T04', None, None, None, None),\n",
       " ('co1.CO.i', 14, None, 'co12004.T03', None, None, None),\n",
       " ('co1.CO.i', 15, None, None, 'co12005.T04', None, None),\n",
       " ('co1.CO.j', 12, 'co12006.T04', None, None, None, None),\n",
       " ('co1.CO.j', 14, None, 'co12004.T03', None, None, None),\n",
       " ('co1.CO.j', 15, None, None, 'co12005.T04', None, None),\n",
       " ('co1.IS', 11, None, None, 'co12004.L01', None, None),\n",
       " ('co1.IS.a', 14, None, None, 'co12004.T04', None, None),\n",
       " ('co1.IS.b', 14, None, None, 'co12004.T04', None, None),\n",
       " ('co1.SE.pt', 10, 'co12006.T05', None, None, None, None),\n",
       " ('co1.SE.pt', 12, 'co12004.T01', None, None, None, None),\n",
       " ('co1.SE.pt', 14, 'co12005.L01', None, None, None, None),\n",
       " ('co1.SE.pt', 16, 'co12005.T05', None, None, None, None),\n",
       " ('co1.SE.pt', 17, 'co12004.L02', None, None, None, None),\n",
       " ('co2.CO', 10, 'co22007.L01', None, None, None, None),\n",
       " ('co2.CO', 13, None, None, None, 'co22005.L01', None),\n",
       " ('co2.CO', 16, None, None, None, None, 'co22006.L01'),\n",
       " ('co2.CO.a', 9, None, None, None, None, 'co22005.T05'),\n",
       " ('co2.CO.a', 12, None, None, None, None, 'co22007.T01'),\n",
       " ('co2.CO.a', 14, 'co22006.T01', None, None, 'co22005.T01', None),\n",
       " ('co2.CO.b', 9, None, None, None, None, 'co22005.T05'),\n",
       " ('co2.CO.b', 14, 'co22007.T02', None, None, 'co22005.T01', None),\n",
       " ('co2.CO.b', 16, 'co22006.T02', None, None, None, None),\n",
       " ('co2.CO.c', 11, None, 'co22007.T03', 'co22005.T06', None, None),\n",
       " ('co2.CO.c', 13, None, None, 'co22005.T02', None, None),\n",
       " ('co2.CO.c', 16, 'co22006.T03', None, None, None, None),\n",
       " ('co2.CO.d', 11, None, None, 'co22005.T06', None, None),\n",
       " ('co2.CO.d', 13, None, None, 'co22005.T02', None, None),\n",
       " ('co2.CO.d', 16, None, 'co22007.T04', 'co22006.T04', None, None),\n",
       " ('co2.CO.e', 10, None, None, None, None, 'co22005.T10'),\n",
       " ('co2.CO.e', 12, None, None, None, None, 'co22007.T01'),\n",
       " ('co2.CO.e', 14, 'co22006.T01', None, None, None, None),\n",
       " ('co2.CO.e', 16, None, None, None, 'co22005.T09', None),\n",
       " ('co2.CO.i', 11, None, None, 'co22005.T03', None, None),\n",
       " ('co2.CO.i', 13, None, None, 'co22009.L01', None, None),\n",
       " ('co2.CO.i', 14, 'co22007.T02', None, 'co22005.T07', None, None),\n",
       " ('co2.CO.i', 16, 'co22006.T02', None, 'co22009.T01', None, None),\n",
       " ('co2.CO.j', 11, None, 'co22007.T03', 'co22005.T03', None, None),\n",
       " ('co2.CO.j', 13, None, None, 'co22009.L01', None, None),\n",
       " ('co2.CO.j', 14, None, None, 'co22005.T07', None, None),\n",
       " ('co2.CO.j', 16, 'co22006.T03', None, 'co22009.T01', None, None),\n",
       " ('co2.CO.k', 9, None, 'co22005.T04', None, None, None),\n",
       " ('co2.CO.k', 11, None, 'co22005.T08', None, None, None),\n",
       " ('co2.CO.k', 13, None, None, 'co22009.L01', None, None),\n",
       " ('co2.CO.k', 16, None, 'co22007.T04', 'co22006.T04', None, None),\n",
       " ('co2.IS', 9, None, 'co22005.T04', None, None, None),\n",
       " ('co2.IS', 10, 'co22007.L01', None, None, None, None),\n",
       " ('co2.IS', 11, None, 'co22005.T08', None, None, None),\n",
       " ('co2.IS', 13, None, None, None, 'co22005.L01', None),\n",
       " ('co2.IS', 15, None, None, None, 'co22007.T06', None),\n",
       " ('co2.NT.Ea', 13, None, None, None, 'co22020.T01', None),\n",
       " ('co2.NT.Ea', 14, None, None, None, None, 'co22020.L01'),\n",
       " ('co2.NT.Eb', 14, None, None, None, None, 'co22020.L01'),\n",
       " ('co2.NT.Eb', 16, None, None, None, None, 'co22020.T02'),\n",
       " ('co2.NT.Ec', 14, None, 'co22020.T03', None, None, 'co22020.L01'),\n",
       " ('co2.NT.Ed', 12, None, None, None, None, 'co22020.T04'),\n",
       " ('co2.NT.Ed', 14, None, None, None, None, 'co22020.L01'),\n",
       " ('co2.NT.Ee', 10, None, None, None, None, 'co22020.T05'),\n",
       " ('co2.NT.Ee', 14, None, None, None, None, 'co22020.L01'),\n",
       " ('co2.SE.pt', 10, None, None, None, 'co12006.L02', None),\n",
       " ('co2.SE.pt', 13, None, None, None, 'co22005.L01', None),\n",
       " ('co2.SE.pt', 15, None, None, None, 'co12006.T06', None),\n",
       " ('co2.SE.pt', 16, None, None, None, 'co22005.T09', None),\n",
       " ('co2.SE.pt', 17, None, None, None, 'co22007.T05', None),\n",
       " ('co3.CO', 9, None, None, 'co32014.T01', None, None),\n",
       " ('co3.CO', 11, None, None, None, 'co32011.L01', None),\n",
       " ('co3.CO', 12, None, 'co32016.L01', None, 'co32011.T01', None),\n",
       " ('co3.CO', 13, None, None, None, 'co32011.L02', None),\n",
       " ('co3.CO', 15, None, 'co32014.L01', None, None, None),\n",
       " ('co3.DMM', 9, None, None, 'co32021.L01', None, None),\n",
       " ('co3.DMM', 10, 'co22007.L01', 'co32018.L01', None, None, None),\n",
       " ('co3.DMM', 12, None, None, None, 'co32005.T05', None),\n",
       " ('co3.DMM', 13, None, None, None, None, 'co32018.T01'),\n",
       " ('co3.DMM', 15, None, None, None, 'co22007.T06', None),\n",
       " ('co3.DMM', 16, None, None, None, None, 'co32005.L01'),\n",
       " ('co3.DNC', 9, None, None, 'co32021.L01', None, None),\n",
       " ('co3.DNC', 10, None, 'co32018.L01', 'co32005.T04', None, None),\n",
       " ('co3.DNC', 11, None, None, None, None, 'co32003.T01'),\n",
       " ('co3.DNC', 13, None, None, None, None, 'co32018.T01'),\n",
       " ('co3.DNC', 14, None, 'co32003.L01', None, None, None),\n",
       " ('co3.DNC', 16, None, None, None, None, 'co32005.L01'),\n",
       " ('co3.DST', 9, None, None, 'co32021.L01', None, None),\n",
       " ('co3.DST', 10, None, 'co32018.L01', None, None, None),\n",
       " ('co3.DST', 12, 'co32023.L01', None, 'co32023.T01', 'co32005.T05', None),\n",
       " ('co3.DST', 13, None, None, None, None, 'co32018.T01'),\n",
       " ('co3.DST', 16, None, None, None, None, 'co32005.L01'),\n",
       " ('co3.MM', 11, None, 'co32011.T03', None, 'co32011.L01', None),\n",
       " ('co3.MM', 12, None, 'co32016.L01', None, None, None),\n",
       " ('co3.MM', 13, None, None, None, 'co32011.L02', None),\n",
       " ('co3.MM', 15, None, 'co32014.L01', None, None, None),\n",
       " ('co3.MM', 16, None, 'co32014.T03', None, None, None),\n",
       " ('co3.ND', 9, None, None, 'co32014.T01', None, None),\n",
       " ('co3.ND', 10, None, 'co32011.T02', None, None, None),\n",
       " ('co3.ND', 11, None, None, None, 'co32011.L01', None),\n",
       " ('co3.ND', 12, None, 'co32016.L01', None, None, None),\n",
       " ('co3.ND', 13, None, None, None, 'co32011.L02', None),\n",
       " ('co3.ND', 15, None, 'co32014.L01', None, None, None),\n",
       " ('co3.SE', 10, None, 'co32011.T02', None, None, None),\n",
       " ('co3.SE', 11, None, None, None, 'co32011.L01', None),\n",
       " ('co3.SE', 12, None, 'co32016.L01', None, None, None),\n",
       " ('co3.SE', 13, None, None, None, 'co32011.L02', None),\n",
       " ('co3.SE', 15, None, 'co32014.L01', None, None, None),\n",
       " ('co3.SE', 16, 'co32014.T02', None, None, None, None),\n",
       " ('co3.SE.pt', 10, None, None, None, 'co22006.L02', None),\n",
       " ('co3.SE.pt', 11, None, None, None, 'co32011.L01', None),\n",
       " ('co3.SE.pt', 12, None, None, None, 'co32011.T01', None),\n",
       " ('co3.SE.pt', 13, None, None, None, 'co32011.L02', None),\n",
       " ('co3.SE.pt', 15, None, None, None, 'co22006.L03', None),\n",
       " ('co3.SE.pt', 17, None, None, None, 'co22007.T05', None),\n",
       " ('co4.CO', 13, None, None, None, None, 'co42009.T01'),\n",
       " ('co4.CO', 14, None, 'co32015.L01', None, None, 'co32015.T01'),\n",
       " ('co4.CO', 15, 'co42009.L01', None, None, None, None),\n",
       " ('co4.CO', 16, None, None, None, 'co42010.L01', 'co42019.L01'),\n",
       " ('co4.CO', 17, None, None, None, 'co42010.T02', None),\n",
       " ('co4.DNC', 9, None, None, 'co32014.T01', None, None),\n",
       " ('co4.DNC', 11, None, 'co42005.L01', None, 'co42022.T01', None),\n",
       " ('co4.DNC', 14, None, None, None, 'co42022.L01', None),\n",
       " ('co4.DNC', 15, None, 'co32014.L01', None, 'co42005.T01', None),\n",
       " ('co4.DNC', 16, None, None, None, None, 'co42019.L01'),\n",
       " ('co4.IS', 11, None, 'co42005.L01', None, None, None),\n",
       " ('co4.IS', 15, None, None, None, 'co42005.T01', None),\n",
       " ('co4.IS', 16, None, None, None, None, 'co42019.L01'),\n",
       " ('co4.MM', 11, None, 'co42005.L01', None, None, None),\n",
       " ('co4.MM', 13, None, 'co42002.T01', None, None, None),\n",
       " ('co4.MM', 15, None, None, 'co42001.L01', 'co42005.T01', None),\n",
       " ('co4.MM', 16, None, 'co42002.L01', None, 'co42001.T01', 'co42019.L01'),\n",
       " ('co4.ND', 10, None, None, None, 'co42015.T01', None),\n",
       " ('co4.ND', 13, None, None, None, None, 'co42009.T01'),\n",
       " ('co4.ND', 15, 'co42009.L01', None, 'co42001.L01', None, None),\n",
       " ('co4.ND', 16, None, None, None, 'co42001.T01', 'co42019.L01'),\n",
       " ('co4.ND', 17, None, 'co42015.L01', None, None, None),\n",
       " ('co4.SE', 10, None, None, None, 'co42013.L01', None),\n",
       " ('co4.SE', 11, None, None, None, 'co42013.T01', None),\n",
       " ('co4.SE', 12, None, 'co42010.T01', None, None, None),\n",
       " ('co4.SE', 16, None, None, None, 'co42010.L01', 'co42019.L01'),\n",
       " ('co4.SE.pt', 12, None, 'co32016.L01', None, None, None),\n",
       " ('co4.SE.pt', 15, None, 'co32014.L01', None, None, None),\n",
       " ('co4.SE.pt', 16, None, 'co32014.T03', None, None, None),\n",
       " ('co5.SE.pt', 10, None, None, None, 'co42013.L01', None),\n",
       " ('co5.SE.pt', 11, None, None, None, 'co42013.T01', None),\n",
       " ('co5.SE.pt', 16, None, None, None, 'co42010.L01', None),\n",
       " ('co5.SE.pt', 17, None, None, None, 'co42010.T02', None),\n",
       " ('coh.HND1', 9, None, None, None, None, 'coh8412615.L01'),\n",
       " ('coh.HND1', 12, None, None, 'coh8412585.L01', None, 'coh8412635.L02'),\n",
       " ('coh.HND1', 14, None, None, None, 'coh8412635.L01', None),\n",
       " ('coh.HND1', 15, None, None, 'coh8412605.L01', None, None),\n",
       " ('coh.HND1', 17, None, None, None, 'coh8560095.L01', None),\n",
       " ('coh.HND1a', 11, None, 'coh8412635.T04', None, None, None),\n",
       " ('coh.HND1a', 15, None, 'coh8412615.T04', None, None, None),\n",
       " ('coh.HND1a', 16, 'coh8412635.T01', None, None, 'coh8560095.T01', 'coh8412615.T01'),\n",
       " ('coh.HND1a', 17, None, None, 'coh8412605.T01', None, None),\n",
       " ('coh.HND1b', 11, None, None, None, None, 'coh8412635.T02'),\n",
       " ('coh.HND1b', 12, None, None, None, 'coh8560095.T02', None),\n",
       " ('coh.HND1b', 15, 'coh8412585.T02', None, None, None, None),\n",
       " ('coh.HND1b', 16, None, 'coh8412605.T02', None, 'coh8412615.T02', 'coh8412635.T05'),\n",
       " ('coh.HND1b', 17, None, None, 'coh8412615.T05', None, None),\n",
       " ('coh.HND1c', 10, None, 'coh8412585.T03', None, None, None),\n",
       " ('coh.HND1c', 11, None, None, 'coh8560095.T03', None, None),\n",
       " ('coh.HND1c', 12, None, 'coh8412615.T03', None, 'coh8412635.T03', None),\n",
       " ('coh.HND1c', 15, None, None, None, None, 'coh8412605.T03'),\n",
       " ('coh.HND1c', 17, 'coh8412615.T06', None, None, None, None),\n",
       " ('coh.HND2', 9, 'coh6412255.L01', None, 'coh8412555.L01', 'coh8412555.L02', None),\n",
       " ('coh.HND2', 10, None, 'coh8412555.T02', None, None, None),\n",
       " ('coh.HND2', 11, None, None, None, 'coh8521005.L01', None),\n",
       " ('coh.HND2', 12, 'coh8412575.T02', None, 'coh8412575.L01', None, None),\n",
       " ('coh.HND2', 15, None, 'coh8412575.T01', None, None, None),\n",
       " ('coh.HND2', 16, None, None, 'coh6412255.T01', None, None),\n",
       " ('coh.HND2', 17, None, None, None, 'coh8412555.T01', None),\n",
       " ('coh.HND2a', 14, None, None, None, None, 'coh8521005.T01'),\n",
       " ('coh.HND2b', 11, None, None, None, None, 'coh8521005.T02'),\n",
       " ('com.IS', 10, None, None, None, 'co72012.L01', None),\n",
       " ('com.IS', 15, None, None, 'co72013.L01', None, None),\n",
       " ('com.IS.a', 9, None, None, 'co72013.T01', None, None),\n",
       " ('com.IS.a', 12, None, None, 'co72012.T02', None, None),\n",
       " ('com.IS.a', 15, None, 'co72033.L01', None, None, None),\n",
       " ('com.IS.a', 16, None, 'co72033.T01', None, None, None),\n",
       " ('com.IS.b', 9, None, 'co72016.T01', 'co72013.T01', None, None),\n",
       " ('com.IS.b', 11, None, 'co72016.L01', None, None, None),\n",
       " ('com.IS.b', 12, None, None, 'co72012.T02', None, None),\n",
       " ('com.IS.d', 12, None, 'co72013.T02', None, None, 'co72012.T01'),\n",
       " ('com.IS.e', 11, None, 'co72017.L01', None, None, 'co72017.T01'),\n",
       " ('com.IS.e', 12, None, None, None, 'co72012.T03', None),\n",
       " ('com.IS.e', 13, None, None, 'co72013.T06', None, None),\n",
       " ('com.IS.th', 10, None, None, None, 'co72012.L01', None),\n",
       " ('com.IS.th', 12, None, None, None, 'co72012.T03', None),\n",
       " ('com.IS.tu', 10, None, 'co72013.L02', None, None, None),\n",
       " ('com.IS.tu', 11, None, 'co72013.T03', None, None, None),\n",
       " ('com.IS.tu', 15, None, 'co72033.L01', None, None, None),\n",
       " ('com.IS.tu', 16, None, 'co72033.T01', None, None, None),\n",
       " ('com.ITFS', 11, 'co72002.L01', None, None, None, None),\n",
       " ('com.ITFS', 12, 'co72002.T01', None, None, None, None),\n",
       " ('com.ITFS', 15, 'co72003.L01', None, None, None, None),\n",
       " ('com.ITeC', 13, 'co72026.T01', None, None, None, None),\n",
       " ('com.ITeC', 16, 'co72026.L01', None, None, None, None),\n",
       " ('com.ITeC.feb', 11, None, None, None, 'co72023.T03', None),\n",
       " ('com.ITeC.feb', 12, None, 'co72013.T02', None, None, None),\n",
       " ('com.ITeC.feb', 13, None, None, None, None, 'co72011.T09'),\n",
       " ('com.ITeC.feb', 15, None, None, 'co72013.L01', None, None),\n",
       " ('com.ITeC.feb', 16, 'co72011.L01', None, None, None, None),\n",
       " ('com.ITeC.pt2', 17, None, None, None, None, 'co72023.T05'),\n",
       " ('com.ITeC.z1', 19, None, 'co72023.L02', None, None, None),\n",
       " ('com.ITeC.z1', 20, None, 'co72023.T06', None, None, None),\n",
       " ('com.ITeC.z2', 19, None, 'co72023.L02', None, None, None),\n",
       " ('com.ITeC.z2', 20, None, 'co72023.T06', None, None, None),\n",
       " ('com.ITeC.z3', 19, None, 'co72026.L02', None, None, None),\n",
       " ('com.MM', 16, 'co72011.L01', None, None, None, None),\n",
       " ('com.MM.a', 9, None, None, None, None, 'co72011.T01'),\n",
       " ('com.MM.a', 14, None, None, None, 'co72023.T01', None),\n",
       " ('com.MM.b', 13, 'co72011.T02', None, None, None, None),\n",
       " ('com.MM.b', 16, None, None, None, 'co72023.T02', None),\n",
       " ('com.MM.c', 11, None, None, None, 'co72023.T03', None),\n",
       " ('com.MM.c', 13, None, None, None, 'co72011.T03', None),\n",
       " ('com.MM.d', 14, 'co72011.T04', None, None, None, None),\n",
       " ('com.MM.d', 16, None, None, None, None, 'co72023.T04'),\n",
       " ('com.MM.feb', 10, None, None, 'co72006.L01', None, None),\n",
       " ('com.MM.feb', 12, None, None, 'co72006.T01', None, None),\n",
       " ('com.MM.feb', 14, None, None, None, 'co72004.L01', None),\n",
       " ('com.MM.feb', 15, None, None, None, 'co72004.T01', None),\n",
       " ('com.MM.pt2', 14, 'co72011.T04', None, None, None, None),\n",
       " ('com.MM.pt2', 16, 'co72011.L01', None, None, None, 'co72023.T04'),\n",
       " ('com.MM.pt3', 10, None, None, 'co72006.L01', None, None),\n",
       " ('com.MM.pt3', 12, None, None, 'co72006.T01', None, None),\n",
       " ('com.MM.pt3', 14, None, None, None, 'co72004.L01', None),\n",
       " ('com.MM.pt3', 15, None, None, None, 'co72004.T01', None),\n",
       " ('com.SE', 10, None, None, None, 'co72013.T05', None),\n",
       " ('com.SE', 12, 'co72018.T02', None, None, 'co72018.L01', None),\n",
       " ('com.SE', 14, None, 'co72021.L01', None, None, None),\n",
       " ('com.SE', 15, None, None, 'co72013.L01', None, None),\n",
       " ('com.SE.th', 10, None, None, None, 'co72018.T01', None),\n",
       " ('com.SE.th', 12, None, None, None, 'co72018.L01', None),\n",
       " ('com.ST', 10, None, None, None, 'co72018.T01', None),\n",
       " ('com.ST', 12, None, None, None, 'co72018.L01', None),\n",
       " ('com.ST', 15, None, None, 'co72013.L01', None, None),\n",
       " ('com.ST', 16, None, None, 'co72013.T04', None, None),\n",
       " ('com.ST.a', 15, None, 'co72033.L01', None, None, None),\n",
       " ('com.ST.a', 16, None, 'co72033.T01', None, None, None),\n",
       " ('com.ST.b', 9, None, 'co72016.T01', None, None, None),\n",
       " ('com.ST.b', 11, None, 'co72016.L01', None, None, None),\n",
       " ('com.ST.th', 10, None, None, None, 'co72018.T01', None),\n",
       " ('com.ST.th', 12, None, None, None, 'co72018.L01', None),\n",
       " ('com.ST.tu', 10, None, 'co72013.L02', None, None, None),\n",
       " ('com.ST.tu', 11, None, 'co72013.T03', None, None, None),\n",
       " ('com.ST.tu', 15, None, 'co72033.L01', None, None, None),\n",
       " ('com.ST.tu', 16, None, 'co72033.T01', None, None, None)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (-- vague requirements\n",
    "    SELECT ut_event.id, student, dow, CAST(SPLIT(tod, ':')[0] AS INT) tod\n",
    "      FROM ut_event JOIN ut_occurs ON (ut_event.id=ut_occurs.event)\n",
    "        JOIN ut_attends ON (ut_attends.event=ut_event.id)\n",
    "        WHERE ut_occurs.week='01' AND duration=1\n",
    "    UNION ALL\n",
    "    SELECT ut_event.id, student, dow, CAST(SPLIT(tod, ':')[0] AS INT) + 1 AS tod\n",
    "      FROM ut_event JOIN ut_occurs ON (ut_event.id=ut_occurs.event)\n",
    "        JOIN ut_attends ON (ut_attends.event=ut_event.id)\n",
    "        WHERE ut_occurs.week='01' AND duration=2\n",
    ")\n",
    "SELECT student, tod,\n",
    "  MAX(CASE WHEN dow='Monday' THEN id ELSE NULL END) mon,\n",
    "    MAX(CASE WHEN dow='Tuesday' THEN id ELSE NULL END) tue,\n",
    "    MAX(CASE WHEN dow='Wednesday' THEN id ELSE NULL END) wed,\n",
    "    MAX(CASE WHEN dow='Thursday' THEN id ELSE NULL END) thu,\n",
    "    MAX(CASE WHEN dow='Friday' THEN id ELSE NULL END) fri\n",
    "    FROM t\n",
    "    GROUP BY student, tod\n",
    "    ORDER BY student, tod"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
